import mariadb
from threading import Condition
from support.utils.toolkit import Log

class Operator:
    def __init__(self, host, port, user, passwd, database):
        self.host = host
        self.port = port
        self.username = user
        self.passwd = passwd
        self.database = database
        self.runningTask = []
        self.conn = None
        self.condition = None

    def __hasTaskRunning__(self, *requests):
        ret = False
        if(len(requests) == 0):
            ret = len(self.runningTask) > 0
        else:
            for task in self.runningTask:
                if(task in requests):
                    ret = True
                    break
        return ret
    
    def __canClose__(self):
        return not self.__hasTaskRunning__()
    
    def __canQuery__(self):
        return not self.__hasTaskRunning__("INSERT","UPDATE","DELETE")
    
    def __canUpdate__(self):
        return not self.__hasTaskRunning__("DELETE")

    def getUserName(self):
        return self.username

    def getPassword(self):
        return self.passwd

    def getDataBaseName(self):
        return self.database

    def __enter__(self):
        if(not self.conn):self.conn = mariadb.connect(user=self.username, password=self.passwd, host=self.host, port=self.port, database=self.database)
        if(not self.condition):self.condition = Condition()

    def __exit__(self, exc_type, exc_val, traceback):
        errorinfo = ""
        if(exc_type is not None):errorinfo += "Type: {}\n".format(exc_type)
        if(exc_type is not None):errorinfo += "Value: {}\n".format(exc_val)
        if(errorinfo): Log.e(errorinfo)
        self.condition.acquire()
        with self.condition:
            self.condition.wait_for(self.__canClose__)
            try:
                if(self.conn is not None):self.conn.close()
                self.conn = None
            except Exception as e:
                Log.e(str(e))
        self.condition.release()
        return True
    
    def execute(self, sqlarray):
        result = []
        self.condition.acquire()
        cur = self.conn.cursor()
        for sql in sqlarray:
            ret = None
            request = sql.split()[0].upper()
            with self.condition:
                if(request == "SELECT"):self.condition.wait_for(self.__canQuery__)
                if(request == "UPDATE"):self.condition.wait_for(self.__canUpdate__)
                self.runningTask.append(request)
                try:
                    cur.execute(sql)
                    if(request == "SELECT"):ret = cur.fetchall()
                    if(request == "INSERT"):ret = cur.lastrowid
                    if(request != "SELECT"):self.conn.commit()
                except Exception as e:
                    Log.e("{}|{}".format(sql, str(e)))
                result.append(ret)
                self.runningTask.remove(request)
        cur.close()
        self.condition.notify_all()
        self.condition.release()
        return result

class DBMS:
    __instance__ = None
    def __new__(cls, *args, **kwargs):
        if(not cls.__instance__): cls.__instance__ = object.__new__(cls)
        return cls.__instance__

    def getInstance():
        return DBMS.__instance__

    def __init__(self, user, passwd, host):
        try:
            getattr(self, "operators")
        except AttributeError:
            self.user = user
            self.passwd = passwd
            self.host = host
            self.operators = {}
    
    def createDatabase(self, oper, name):
        sql = "CREATE DATABASE IF NOT EXISTS {} CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci'".format(name)
        with oper:
            oper.execute([sql])

    def newUser(self, oper, username, passwd, database):
        sqlarray = []
        ret = DBMS.query(oper, "mysql.user", {"USER":username, "HOST":None})
        if(ret):
            sql = "ALTER"
        else:
            sql = "CREATE"
        sql += " USER `{}`@`%` IDENTIFIED BY '{}'".format(username, passwd)
        sqlarray.append(sql)
        sql = "GRANT ALL PRIVILEGES ON `{}`.* to `{}`@`%`".format(database, username)
        sqlarray.append(sql)
        sql = "FLUSH PRIVILEGES"
        sqlarray.append(sql)
        with oper:
            oper.execute(sqlarray)
    
    def createTables(self, oper, tables):
        sqlarray = []
        for table in tables:
            sql = "CREATE TABLE IF NOT EXISTS {} (`ID` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT".format(table["name"])
            for key in table["keys"]:
                sql += ", `{}` {}".format(key["label"],key["type"])
                if("length" in key.keys()): sql+="({})".format(key["length"])
                if("limit" in key.keys()):sql += " " + key["limit"]
            sql +=")"
            sqlarray.append(sql)
        with oper:
            oper.execute(sqlarray)
    
    def connect(self, *, user=None, passwd=None, port=3306, database):
        label="__root__"
        if(database != ""):label = database
        if(label not in self.operators):
            if(not user): user = self.user
            if(not passwd): passwd = self.passwd
            self.operators[label] = Operator(self.host, port, user, passwd, database)
        return self.operators[label]
    
    def query(oper, tablename, dic, *, order=None, limit=None):
        sql = "SELECT "
        keys = []
        where = []
        result = None
        for key in dic.keys():
            if(dic[key] == None):
                keys.append(key)
            else:
                where.append("`{}` = '{}'".format(key, dic[key]))
        if(len(keys)):
            sql += ",".join(keys)
        else:
            sql += "*"
        sql += " FROM " + tablename
        if(len(where)):sql += " WHERE {}".format(" AND ".join(where))
        if(order):sql += " ORDER BY `{}` {}".format(*order)
        if(limit):sql +=" LIMIT {}".format(limit)
        with oper:
            result = oper.execute([sql])
        ret = []
        if(result):
            for item in result[0]:
                tmp = {}
                index = 0
                for key in keys:
                    tmp[key.strip("`")] = item[index]
                    index += 1
                ret.append(tmp)
        return ret

    def insert(oper, tablename, dic):
        key = ",".join("`{}`".format(i) for i in dic.keys())
        value = "','".join(str(i) for i in dic.values())
        sql = "INSERT INTO {}({}) VALUES('{}')".format(tablename, key, value)
        ret = None
        with oper:
            ret = oper.execute([sql])
        return ret[0]
    
    def update(oper, tablename, v, w):
        value = ["`{}` = '{}'".format(k, v[k]) for k in v.keys()]
        where = ["`{}` = '{}'".format(k, w[k]) for k in w.keys()]
        sql = "UPDATE {} SET {} WHERE {}".format(tablename, ",".join(value), " AND ".join(where))
        with oper:
            oper.execute([sql])
            
